<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class finance extends MY_Controller
{
    public function __construct()
    {
        parent::__construct();
        parent::checkLogin();

    }

    //收卡总账信息
    public function financeInfo()
    {
//        $array = array(
//            'aa.agent_id' > 0,
//            'dd.state' => 1
//        );
        $sql = "SELECT MIN(bpm) AS unitPrice, SUM(tf) AS tf, SUM(cwm) AS scwm, SUM(tn) AS stn, SUM(ac) AS sac , SUM(acc) AS cardcount, SUM(sx) AS sdc FROM (SELECT aa.id, MIN(bb.per_money) AS bpm, SUM(bb.total_fee) AS tf, SUM(bb.combom_waste_money) AS cwm, SUM(bb.total_number) AS tn , aa.activity_card AS ac, aa.cardcount AS acc, sd.xcard AS sx FROM Record_Member aa INNER JOIN (SELECT agent_id, combom_waste_money, total_number, total_fee, per_money FROM Record_Orders tt LEFT JOIN Record_Roles RR ON tt.role_id = RR.rid and tt.type = RR.cv  ) bb ON aa.id = bb.agent_id FULL JOIN (SELECT aa.id, SUM(bb.cardcount) AS xcard FROM Record_Member aa INNER JOIN Record_Card bb ON aa.id = bb.agentid GROUP BY aa.id ) sd ON aa.id = sd.id GROUP BY aa.id, aa.activity_card, aa.cardcount, sd.xcard ) result";

        $result = $this->db
            ->query($sql)
            ->result_array();
        $sql1 = "SELECT MIN(bpm) AS unitPrice, SUM(tf) AS tf, SUM(cwm) AS scwm, SUM(tn) AS stn, SUM(ac) AS sac , SUM(acc) AS cardcount, SUM(sx) AS sdc FROM (SELECT aa.id, MIN(bb.per_money) AS bpm, SUM(bb.total_fee) AS tf, SUM(bb.combom_waste_money) AS cwm, SUM(bb.total_number) AS tn , aa.activity_card AS ac, aa.cardcount AS acc, sd.xcard AS sx FROM Record_Member aa INNER JOIN (SELECT agent_id, combom_waste_money, total_number, total_fee, per_money FROM Record_Orders tt LEFT JOIN Record_Roles RR ON tt.role_id = RR.id and tt.type = RR.cv  ) bb ON aa.id = bb.agent_id FULL JOIN (SELECT aa.id, SUM(bb.cardcount) AS xcard FROM Record_Member aa INNER JOIN Record_Card bb ON aa.id = bb.agentid GROUP BY aa.id ) sd ON aa.id = sd.id WHERE aa.id=46 GROUP BY aa.id, aa.activity_card, aa.cardcount, sd.xcard ) result";
        $result1 = $this->db
            ->query($sql1)
            ->result_array();

        $sql3 = "SELECT MIN(bpm) AS unitPrice, SUM(tf) AS tf, SUM(cwm) AS scwm, SUM(tn) AS stn, SUM(ac) AS sac , SUM(acc) AS cardcount, SUM(sx) AS sdc FROM (SELECT RoomCard AS acc, UserID, activity_card AS ac, MIN(bb.per_money) AS bpm, SUM(bb.total_fee) AS tf , SUM(bb.combom_waste_money) AS cwm, SUM(bb.total_number) AS tn, sd.xcard AS sx FROM QPAccountsDB.dbo.AccountsInfo aa INNER JOIN (SELECT agent_id, combom_waste_money, total_number, total_fee, per_money FROM Record_Orders tt LEFT JOIN Record_Roles RR ON tt.role_id = RR.id and tt.type = RR.cv ) bb ON aa.UserID = bb.agent_id LEFT JOIN sys_user cc ON aa.UserID = cc.uid FULL JOIN (SELECT bb.uid, SUM(bb.card_number) AS xcard FROM QPAccountsDB.dbo.AccountsInfo aa INNER JOIN sys_user_cost bb ON aa.UserID = bb.uid GROUP BY bb.uid ) sd ON aa.UserID = sd.uid GROUP BY aa.UserID, RoomCard, activity_card, sd.xcard ) result";


        $result2 = $this->db
            ->query($sql3)
            ->result_array();
//                var_dump($result2);
//            die;
        $data['data'] = array($result, $_SESSION, $result1, $result2);


//        var_dump($data);
//        die;
        $this->load->view('head.html', $data);
        $this->load->view('financeinfo.html');
        $this->load->view('footer.html');
    }

//售卡信息列表
    public function fianceInfolList()
    {
        $type = $this->uri->segment(3);
        switch ($type) {
            case 1:
                $sql = "SELECT aa.id, MIN(bb.per_money) AS bpm, SUM(bb.total_fee) AS tf, SUM(bb.combom_waste_money) AS cwm, SUM(bb.total_number) AS tn , aa.activity_card AS ac, aa.cardcount AS acc, sd.xcard AS sx FROM Record_Member aa INNER JOIN (SELECT agent_id, combom_waste_money, total_number, total_fee, per_money FROM Record_Orders tt LEFT JOIN Record_Roles RR ON tt.role_id = RR.id and tt.type = RR.cv ) bb ON aa.id = bb.agent_id FULL JOIN (SELECT aa.id, SUM(bb.cardcount) AS xcard FROM Record_Member aa INNER JOIN Record_Card bb ON aa.id = bb.agentid GROUP BY aa.id ) sd ON aa.id = sd.id GROUP BY aa.id, aa.activity_card, aa.cardcount, sd.xcard";
                break;
            case 3:
                $sql = "SELECT aa.id, MIN(bb.per_money) AS bpm, SUM(bb.total_fee) AS tf, SUM(bb.combom_waste_money) AS cwm, SUM(bb.total_number) AS tn , aa.activity_card AS ac, aa.cardcount AS acc, sd.xcard AS sx FROM Record_Member aa INNER JOIN (SELECT agent_id, combom_waste_money, total_number, total_fee, per_money FROM Record_Orders tt LEFT JOIN Record_Roles RR ON tt.role_id = RR.id  and tt.type = RR.cv) bb ON aa.id = bb.agent_id FULL JOIN (SELECT aa.id, SUM(bb.cardcount) AS xcard FROM Record_Member aa INNER JOIN Record_Card bb ON aa.id = bb.agentid GROUP BY aa.id ) sd ON aa.id = sd.id WHERE aa.id=46 GROUP BY aa.id, aa.activity_card, aa.cardcount, sd.xcard ";
                break;
            case 2:
                $sql = "SELECT RoomCard AS acc, UserID id, activity_card AS ac, MIN(bb.per_money) AS bpm, SUM(bb.total_fee) AS tf , SUM(bb.combom_waste_money) AS cwm, SUM(bb.total_number) AS tn, sd.xcard AS sx FROM QPAccountsDB.dbo.AccountsInfo aa INNER JOIN (SELECT agent_id, combom_waste_money, total_number, total_fee, per_money FROM Record_Orders tt LEFT JOIN Record_Roles RR ON tt.role_id = RR.id and tt.type = RR.cv) bb ON aa.UserID = bb.agent_id LEFT JOIN sys_user cc ON aa.UserID = cc.uid FULL JOIN (SELECT bb.uid, SUM(bb.card_number) AS xcard FROM QPAccountsDB.dbo.AccountsInfo aa INNER JOIN sys_user_cost bb ON aa.UserID = bb.uid GROUP BY bb.uid ) sd ON aa.UserID = sd.uid GROUP BY aa.UserID, RoomCard, activity_card, sd.xcard ";
                break;
        }
        $result = $this->db
            ->query($sql)
            ->result_array();


        $data['data'] = array($result, $_SESSION);
        $this->load->view('head.html', $data);
        $this->load->view('fianceInfolList.html');
        $this->load->view('footer.html');
//        var_dump($result);
//        die;
//        echo 11;
    }

//返利信息列表

    public function interestBack()
    {
        $sql = "SELECT aa.id, aa.nickname, aa.lid, re.ad, re.adgp , re.rrgp, re.rr, res.xtotalFee, ress.xxtotalFee, SUM(cc.total_fee) AS totalFee FROM Record_Member aa LEFT JOIN (SELECT tt.id, tt.ad, dd.adgp, dd.rrgp, tt.rr FROM (SELECT aa.id, bb.id AS ad, bb.rebate_rate AS rr FROM sys_agent_level aa LEFT JOIN sys_rebate_level bb ON aa.srs_level = bb.id ) tt LEFT JOIN (SELECT aa.id, bb.id AS adgp, bb.rebate_rate AS rrgp FROM sys_agent_level aa LEFT JOIN sys_rebate_level bb ON aa.srs_group_level = bb.id ) dd ON tt.id = dd.id ) re ON aa.lid = re.id LEFT JOIN Record_Orders cc ON aa.id = cc.agent_id LEFT JOIN (SELECT tt.id, SUM(total_fee) AS xtotalFee FROM (SELECT aa.id, bb.agentid FROM Record_Member aa INNER JOIN Record_Agent bb ON aa.id = bb.father_id WHERE aa.id <> bb.agentid ) tt LEFT JOIN Record_Orders RO ON RO.agent_id = tt.agentid WHERE RO.is_pay_up = '0' GROUP BY tt.id ) res ON aa.id = res.id LEFT JOIN (SELECT ttt.id, SUM(total_fee) AS xxtotalFee FROM (SELECT tt.id, gg.agentid FROM (SELECT aa.id, bb.agentid FROM Record_Member aa LEFT JOIN Record_Agent bb ON aa.id = bb.father_id WHERE aa.id <> bb.agentid ) tt INNER JOIN Record_Agent gg ON tt.agentid = gg.father_id ) ttt LEFT JOIN Record_Orders yy ON ttt.agentid = yy.agent_id WHERE yy.is_pay_uper = '0' GROUP BY ttt.id ) ress ON aa.id = ress.id WHERE cc.is_pay = '0' GROUP BY aa.id, aa.nickname, aa.lid, re.ad, re.adgp, re.rrgp, re.rr, res.xtotalFee, ress.xxtotalFee";

        $result = $this->db
            ->query($sql)
            ->result_array();

        $data['data'] = array($result, $_SESSION);

        $this->load->view('head.html', $data);
        $this->load->view('interestBackList.html');
        $this->load->view('footer.html');
    }

//获取所有代理信息
    public function interestBackById()
    {

        $id = $this->uri->segment(3);
        $type = $this->uri->segment(4);
        if ($type == 0) {
            $sql = "SELECT * FROM ( SELECT agentid, father_id, re.lid, totalFee, RM.nickname , re.nickname AS xname, father_id AS aid FROM (SELECT agentid, father_id, lid, SUM(total_fee) AS totalFee, nickname FROM (SELECT bb.agentid, bb.father_id, aa.lid, nickname FROM Record_Member aa LEFT JOIN Record_Agent bb ON aa.id = bb.agentid WHERE aa.id <> bb.father_id ) dd LEFT JOIN Record_Orders bb ON dd.agentid = bb.agent_id WHERE is_pay_up = '0' GROUP BY agentid, father_id, lid, nickname ) re LEFT JOIN Record_Member RM ON re.father_id = RM.id UNION SELECT RA.agentid, RA.father_id, lid, totalFee, nickname , xname, res.aid FROM (SELECT agentid, father_id, re.lid, totalFee, RM.nickname , re.nickname AS xname, father_id AS aid FROM (SELECT agentid, father_id, lid, SUM(total_fee) AS totalFee, nickname FROM (SELECT bb.agentid, bb.father_id, aa.lid, nickname FROM Record_Member aa LEFT JOIN Record_Agent bb ON aa.id = bb.agentid WHERE aa.id <> bb.father_id ) dd LEFT JOIN Record_Orders bb ON dd.agentid = bb.agent_id WHERE is_pay_up = '0' GROUP BY agentid, father_id, lid, nickname ) re LEFT JOIN Record_Member RM ON re.father_id = RM.id ) res INNER JOIN Record_Agent RA ON res.agentid = RA.father_id ) da where aid=$id";
        } else {
            $sql = "SELECT * FROM ( SELECT agentid, father_id, re.lid, totalFee, RM.nickname , re.nickname AS xname, father_id AS aid FROM (SELECT agentid, father_id, lid, SUM(total_fee) AS totalFee, nickname FROM (SELECT bb.agentid, bb.father_id, aa.lid, nickname FROM Record_Member aa LEFT JOIN Record_Agent bb ON aa.id = bb.agentid WHERE aa.id <> bb.father_id ) dd LEFT JOIN Record_Orders bb ON dd.agentid = bb.agent_id WHERE is_pay_up != '0' GROUP BY agentid, father_id, lid, nickname ) re LEFT JOIN Record_Member RM ON re.father_id = RM.id UNION SELECT RA.agentid, RA.father_id, lid, totalFee, nickname , xname, res.aid FROM (SELECT agentid, father_id, re.lid, totalFee, RM.nickname , re.nickname AS xname, father_id AS aid FROM (SELECT agentid, father_id, lid, SUM(total_fee) AS totalFee, nickname FROM (SELECT bb.agentid, bb.father_id, aa.lid, nickname FROM Record_Member aa LEFT JOIN Record_Agent bb ON aa.id = bb.agentid WHERE aa.id <> bb.father_id ) dd LEFT JOIN Record_Orders bb ON dd.agentid = bb.agent_id WHERE is_pay_up != '0' GROUP BY agentid, father_id, lid, nickname ) re LEFT JOIN Record_Member RM ON re.father_id = RM.id ) res INNER JOIN Record_Agent RA ON res.agentid = RA.father_id ) da where aid=$id";

        }


        $result = $this->db
            ->query($sql)
            ->result_array();

        $data['data'] = array($result, $_SESSION);
        $this->load->view('head.html', $data);
        $this->load->view('interestBackListDetail.html');
        $this->load->view('footer.html');
    }

    //代理结算

    public function doInterestBackById()
    {
        $id = $this->uri->segment(3);

        $sql = "SELECT aa.id, aa.nickname, aa.lid, re.ad, re.adgp , re.rrgp, re.rr, res.xtotalFee, ress.xxtotalFee, SUM(cc.total_fee) AS totalFee FROM Record_Member aa LEFT JOIN (SELECT tt.id, tt.ad, dd.adgp, dd.rrgp, tt.rr FROM (SELECT aa.id, bb.id AS ad, bb.rebate_rate AS rr FROM sys_agent_level aa LEFT JOIN sys_rebate_level bb ON aa.srs_level = bb.id ) tt LEFT JOIN (SELECT aa.id, bb.id AS adgp, bb.rebate_rate AS rrgp FROM sys_agent_level aa LEFT JOIN sys_rebate_level bb ON aa.srs_group_level = bb.id ) dd ON tt.id = dd.id ) re ON aa.lid = re.id LEFT JOIN Record_Orders cc ON aa.id = cc.agent_id LEFT JOIN (SELECT tt.id, SUM(total_fee) AS xtotalFee FROM (SELECT aa.id, bb.agentid FROM Record_Member aa INNER JOIN Record_Agent bb ON aa.id = bb.father_id WHERE aa.id <> bb.agentid ) tt LEFT JOIN Record_Orders RO ON RO.agent_id = tt.agentid WHERE RO.is_pay_up = '0' GROUP BY tt.id ) res ON aa.id = res.id LEFT JOIN (SELECT ttt.id, SUM(total_fee) AS xxtotalFee FROM (SELECT tt.id, gg.agentid FROM (SELECT aa.id, bb.agentid FROM Record_Member aa LEFT JOIN Record_Agent bb ON aa.id = bb.father_id WHERE aa.id <> bb.agentid ) tt INNER JOIN Record_Agent gg ON tt.agentid = gg.father_id ) ttt LEFT JOIN Record_Orders yy ON ttt.agentid = yy.agent_id WHERE yy.is_pay_uper = '0' GROUP BY ttt.id ) ress ON aa.id = ress.id WHERE cc.is_pay = '0' and aa.id=$id GROUP BY aa.id, aa.nickname, aa.lid, re.ad, re.adgp, re.rrgp, re.rr, res.xtotalFee, ress.xxtotalFee";

        $result = $this->db
            ->query($sql)
            ->result_array();
        $data = $result[0];
        unset($data['id']);

        $data['aid'] = $id;
        $data['r_order'] = 'A' . time();

        $ret = $this->db->insert('sys_agent_interest', $data);
        $res = array('is_pay' => $data['r_order']);
        $ress = array('is_pay_up' => 'B' . time());
        $resss = array('is_pay_uper' => 'C' . time());

        $this->db->where('agent_id', $id)->update('Record_Orders', $res);

        $rettt = $this->db
            ->select('agentid')
            ->where('father_id', $id)->get('Record_Agent')->result_array();
        $data2 = array();
        foreach ($rettt as $arr) {
            array_push($data2, $arr['agentid']);
        }
        //如果data2不存在  不执行
        if ($data2) {
            $this->db->where_in('agent_id', $data2)->update('Record_Orders', $ress);
        }

        $data3 = array();

        $sql = 'SELECT bb.agentid FROM (SELECT aa.id, bb.agentid FROM Record_Member aa LEFT JOIN Record_Agent bb ON aa.id = bb.father_id WHERE bb.father_id <> bb.agentid ) re LEFT JOIN Record_Agent bb ON re.agentid = bb.father_id WHERE bb.father_id <> bb.agentid';
        $resultt = $this->db
            ->query($sql)
            ->result_array();

        foreach ($resultt as $arr) {
            array_push($data3, $arr['agentid']);
        }
        if ($data3) {
            $this->db->where_in('agent_id', $data3)->update('Record_Orders', $resss);
        }
        if ($ret) {
            $this->alert_message(1, '添加成功', site_url('finance/interestBackHistory'));
        } else {
            $this->alert_message(0, '添加失败', site_url('finance/interestBackHistory'));
        }
    }

//历史结算订单

    public function interestBackHistory()
    {
        $array = array();
        $result = $this->db->where($array)->get('sys_agent_interest')->result_array();

        $data['data'] = array($result, $_SESSION);
        $this->load->view('head.html', $data);
        $this->load->view('interestBackHistoryList.html');
        $this->load->view('footer.html');
    }


    //返利等级列表
    public function rebateList()
    {

        $array = array();
//        $array = array("invalid" => 0);
        $result = $this->db
            ->select('id,type,level,rebate_rate,sales_floor sf,sales_ceiling sc')
            ->where($array)
            ->get('sys_rebate_level')->result_array();


        $data['data'] = array($result, $_SESSION);
        $this->load->view('head.html', $data);
        $this->load->view('rebatelist.html');
        $this->load->view('footer.html');
    }

//新增返利等级
    function addrebateLevel()
    {

        $type = $this->input->post('type');
        $level = $this->input->post('level');
        $rebate = $this->input->post('rebate');
        $array = array(
            'level' => $level,
            "rebate_rate" => $rebate,
            'type' => $type

        );
        $ret = $this->db->where($array)->get('sys_rebate_level')->result_array();
        if ($ret) {
            $this->alert_message(0, '返利等级已经存在', site_url('finance/rebateList'));
            exit;
        }
        if ($type == 2) {
            $array['sales_floor'] = $this->input->post('salesCeiling');
            $array['sales_ceiling'] = $this->input->post('salesFloor');
        }
        $ret = $this->db->insert('sys_rebate_level', $array);
        if ($ret) {
            $this->alert_message(1, '添加成功', site_url('finance/rebateList'));
        } else {
            $this->alert_message(0, '添加失败', site_url('finance/rebateList'));
        }
    }


}